"""
Storage service for document analysis data.

Provides unified interface for JSON file storage and SQLite database operations.
Supports caching, backup, and data persistence for analysis results.
"""

import json
import sqlite3
import shutil
from datetime import datetime
from pathlib import Path
from typing import Dict, List, Optional, Any, Union
from contextlib import contextmanager
import threading
import logging

logger = logging.getLogger(__name__)


class StorageService:
    """Unified storage service for JSON and SQLite operations."""

    def __init__(self, config: Dict[str, Any]):
        """Initialize storage service with configuration.

        Args:
            config: Storage configuration dictionary
        """
        self.config = config
        self.data_dir = Path(config.get("data_directory", "data"))
        self.json_dir = self.data_dir / "json"
        self.backup_dir = self.data_dir / "backups"

        # Database configuration
        self.db_path = self.data_dir / config.get("database_path", "analysis_cache.db")
        self.backup_enabled = config.get("backup_enabled", True)
        self.backup_interval_hours = config.get("backup_interval_hours", 24)

        # Thread safety
        self._lock = threading.RLock()

        # Initialize directories
        self._ensure_directories()

        # Initialize database
        self._init_database()

        # Setup backup timer if enabled
        if self.backup_enabled:
            self._setup_backup_scheduler()

    def _ensure_directories(self) -> None:
        """Ensure all required directories exist."""
        for directory in [self.data_dir, self.json_dir, self.backup_dir]:
            directory.mkdir(parents=True, exist_ok=True)

    def _init_database(self) -> None:
        """Initialize SQLite database with required tables."""
        with self._get_db_connection() as conn:
            cursor = conn.cursor()

            # Documents table
            cursor.execute("""
                CREATE TABLE IF NOT EXISTS documents (
                    id TEXT PRIMARY KEY,
                    file_path TEXT NOT NULL,
                    file_name TEXT NOT NULL,
                    document_type TEXT NOT NULL,
                    content TEXT,
                    metadata TEXT,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                )
            """)

            # Analysis results table
            cursor.execute("""
                CREATE TABLE IF NOT EXISTS analysis_results (
                    id TEXT PRIMARY KEY,
                    document_id TEXT NOT NULL,
                    analysis_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    clarity_score TEXT,
                    metrics TEXT,
                    functional_requirements_count INTEGER DEFAULT 0,
                    user_stories_count INTEGER DEFAULT 0,
                    acceptance_criteria_count INTEGER DEFAULT 0,
                    clarity_level TEXT,
                    completeness_percentage REAL DEFAULT 0.0,
                    ambiguity_percentage REAL DEFAULT 0.0,
                    major_issues TEXT,
                    improvement_areas TEXT,
                    FOREIGN KEY (document_id) REFERENCES documents (id)
                )
            """)

            # Clarification items table
            cursor.execute("""
                CREATE TABLE IF NOT EXISTS clarification_items (
                    id TEXT PRIMARY KEY,
                    analysis_result_id TEXT NOT NULL,
                    priority TEXT NOT NULL,
                    clarification_type TEXT NOT NULL,
                    question TEXT NOT NULL,
                    context TEXT,
                    location TEXT,
                    impact_assessment TEXT,
                    category TEXT,
                    status TEXT DEFAULT 'pending',
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    FOREIGN KEY (analysis_result_id) REFERENCES analysis_results (id)
                )
            """)

            # Improvement suggestions table
            cursor.execute("""
                CREATE TABLE IF NOT EXISTS improvement_suggestions (
                    id TEXT PRIMARY KEY,
                    analysis_result_id TEXT NOT NULL,
                    suggestion_type TEXT NOT NULL,
                    impact TEXT NOT NULL,
                    title TEXT NOT NULL,
                    description TEXT,
                    specific_action TEXT,
                    example TEXT,
                    target_section TEXT,
                    target_text TEXT,
                    rationale TEXT,
                    effort_estimate TEXT,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    FOREIGN KEY (analysis_result_id) REFERENCES analysis_results (id)
                )
            """)

            # Indexes for performance
            cursor.execute("CREATE INDEX IF NOT EXISTS idx_documents_file_path ON documents(file_path)")
            cursor.execute("CREATE INDEX IF NOT EXISTS idx_analysis_results_document_id ON analysis_results(document_id)")
            cursor.execute("CREATE INDEX IF NOT EXISTS idx_clarifications_result_id ON clarification_items(analysis_result_id)")
            cursor.execute("CREATE INDEX IF NOT EXISTS idx_suggestions_result_id ON improvement_suggestions(analysis_result_id)")

            conn.commit()
            logger.info("Database initialized successfully")

    @contextmanager
    def _get_db_connection(self):
        """Context manager for database connections."""
        with self._lock:
            conn = sqlite3.connect(self.db_path, check_same_thread=False)
            conn.row_factory = sqlite3.Row
            try:
                yield conn
            finally:
                conn.close()

    # JSON Storage Methods

    def save_json(self, data: Dict[str, Any], filename: str) -> Path:
        """Save data to JSON file.

        Args:
            data: Data to save
            filename: Name of the file (without extension)

        Returns:
            Path to saved file
        """
        with self._lock:
            file_path = self.json_dir / f"{filename}.json"

            # Create backup if file exists
            if file_path.exists():
                self._create_file_backup(file_path)

            with open(file_path, 'w', encoding='utf-8') as f:
                json.dump(data, f, ensure_ascii=False, indent=2, default=str)

            logger.debug(f"JSON data saved to {file_path}")
            return file_path

    def load_json(self, filename: str) -> Optional[Dict[str, Any]]:
        """Load data from JSON file.

        Args:
            filename: Name of the file (without extension)

        Returns:
            Loaded data or None if file doesn't exist
        """
        file_path = self.json_dir / f"{filename}.json"

        if not file_path.exists():
            return None

        try:
            with open(file_path, 'r', encoding='utf-8') as f:
                data = json.load(f)
            logger.debug(f"JSON data loaded from {file_path}")
            return data
        except (json.JSONDecodeError, IOError) as e:
            logger.error(f"Failed to load JSON from {file_path}: {e}")
            return None

    def delete_json(self, filename: str) -> bool:
        """Delete JSON file.

        Args:
            filename: Name of the file (without extension)

        Returns:
            True if deleted successfully
        """
        with self._lock:
            file_path = self.json_dir / f"{filename}.json"

            if file_path.exists():
                self._create_file_backup(file_path)
                file_path.unlink()
                logger.debug(f"JSON file deleted: {file_path}")
                return True

            return False

    # Database Methods

    def save_document(self, document_data: Dict[str, Any]) -> str:
        """Save document to database.

        Args:
            document_data: Document data dictionary

        Returns:
            Document ID
        """
        with self._get_db_connection() as conn:
            cursor = conn.cursor()

            # Check if document exists
            cursor.execute("SELECT id FROM documents WHERE id = ?", (document_data['id'],))
            existing = cursor.fetchone()

            if existing:
                # Update existing document
                cursor.execute("""
                    UPDATE documents SET
                        file_path = ?, file_name = ?, document_type = ?,
                        content = ?, metadata = ?, updated_at = CURRENT_TIMESTAMP
                    WHERE id = ?
                """, (
                    document_data['file_path'],
                    document_data['file_name'],
                    document_data['document_type'],
                    document_data.get('content', ''),
                    json.dumps(document_data.get('metadata', {})),
                    document_data['id']
                ))
            else:
                # Insert new document
                cursor.execute("""
                    INSERT INTO documents (
                        id, file_path, file_name, document_type,
                        content, metadata, created_at, updated_at
                    ) VALUES (?, ?, ?, ?, ?, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
                """, (
                    document_data['id'],
                    document_data['file_path'],
                    document_data['file_name'],
                    document_data['document_type'],
                    document_data.get('content', ''),
                    json.dumps(document_data.get('metadata', {}))
                ))

            conn.commit()
            logger.info(f"Document saved: {document_data['id']}")
            return document_data['id']

    def get_document(self, document_id: str) -> Optional[Dict[str, Any]]:
        """Retrieve document from database.

        Args:
            document_id: ID of document to retrieve

        Returns:
            Document data or None if not found
        """
        with self._get_db_connection() as conn:
            cursor = conn.cursor()
            cursor.execute("SELECT * FROM documents WHERE id = ?", (document_id,))
            row = cursor.fetchone()

            if row:
                document = dict(row)
                if document['metadata']:
                    document['metadata'] = json.loads(document['metadata'])
                return document

            return None

    def save_analysis_result(self, result_data: Dict[str, Any]) -> str:
        """Save analysis result to database.

        Args:
            result_data: Analysis result data

        Returns:
            Analysis result ID
        """
        with self._get_db_connection() as conn:
            cursor = conn.cursor()

            # Serialize complex fields
            clarity_score = json.dumps(result_data.get('clarity_score', {}))
            metrics = json.dumps(result_data.get('metrics', {}))
            major_issues = json.dumps(result_data.get('major_issues', []))
            improvement_areas = json.dumps(result_data.get('improvement_areas', []))

            cursor.execute("""
                INSERT OR REPLACE INTO analysis_results (
                    id, document_id, analysis_date, clarity_score, metrics,
                    functional_requirements_count, user_stories_count,
                    acceptance_criteria_count, clarity_level,
                    completeness_percentage, ambiguity_percentage,
                    major_issues, improvement_areas
                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            """, (
                result_data['id'],
                result_data['document_id'],
                result_data.get('analysis_date', datetime.now()),
                clarity_score,
                metrics,
                result_data.get('functional_requirements_count', 0),
                result_data.get('user_stories_count', 0),
                result_data.get('acceptance_criteria_count', 0),
                result_data.get('clarity_level', ''),
                result_data.get('completeness_percentage', 0.0),
                result_data.get('ambiguity_percentage', 0.0),
                major_issues,
                improvement_areas
            ))

            conn.commit()
            logger.info(f"Analysis result saved: {result_data['id']}")
            return result_data['id']

    def get_analysis_result(self, result_id: str) -> Optional[Dict[str, Any]]:
        """Retrieve analysis result from database.

        Args:
            result_id: ID of analysis result to retrieve

        Returns:
            Analysis result data or None if not found
        """
        with self._get_db_connection() as conn:
            cursor = conn.cursor()
            cursor.execute("SELECT * FROM analysis_results WHERE id = ?", (result_id,))
            row = cursor.fetchone()

            if row:
                result = dict(row)
                # Deserialize complex fields
                if result['clarity_score']:
                    result['clarity_score'] = json.loads(result['clarity_score'])
                if result['metrics']:
                    result['metrics'] = json.loads(result['metrics'])
                if result['major_issues']:
                    result['major_issues'] = json.loads(result['major_issues'])
                if result['improvement_areas']:
                    result['improvement_areas'] = json.loads(result['improvement_areas'])
                return result

            return None

    def get_document_analysis(self, document_id: str) -> Optional[Dict[str, Any]]:
        """Get latest analysis result for a document.

        Args:
            document_id: ID of document

        Returns:
            Latest analysis result or None
        """
        with self._get_db_connection() as conn:
            cursor = conn.cursor()
            cursor.execute("""
                SELECT ar.* FROM analysis_results ar
                WHERE ar.document_id = ?
                ORDER BY ar.analysis_date DESC
                LIMIT 1
            """, (document_id,))
            row = cursor.fetchone()

            if row:
                result = dict(row)
                # Deserialize complex fields
                if result['clarity_score']:
                    result['clarity_score'] = json.loads(result['clarity_score'])
                if result['metrics']:
                    result['metrics'] = json.loads(result['metrics'])
                if result['major_issues']:
                    result['major_issues'] = json.loads(result['major_issues'])
                if result['improvement_areas']:
                    result['improvement_areas'] = json.loads(result['improvement_areas'])
                return result

            return None

    # Backup and Maintenance Methods

    def _create_file_backup(self, file_path: Path) -> None:
        """Create backup of file before modification."""
        if not file_path.exists():
            return

        timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")
        backup_name = f"{file_path.stem}_{timestamp}{file_path.suffix}"
        backup_path = self.backup_dir / backup_name

        try:
            shutil.copy2(file_path, backup_path)
            logger.debug(f"Backup created: {backup_path}")
        except IOError as e:
            logger.warning(f"Failed to create backup of {file_path}: {e}")

    def create_database_backup(self) -> Path:
        """Create a full database backup.

        Returns:
            Path to backup file
        """
        timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")
        backup_path = self.backup_dir / f"analysis_cache_{timestamp}.db"

        with self._lock:
            try:
                shutil.copy2(self.db_path, backup_path)
                logger.info(f"Database backup created: {backup_path}")
                return backup_path
            except IOError as e:
                logger.error(f"Failed to create database backup: {e}")
                raise

    def cleanup_old_backups(self, keep_count: int = 10) -> None:
        """Clean up old backup files, keeping only the most recent ones.

        Args:
            keep_count: Number of recent backups to keep
        """
        with self._lock:
            # Clean up database backups
            db_backups = sorted(self.backup_dir.glob("analysis_cache_*.db"),
                              key=lambda p: p.stat().st_mtime, reverse=True)

            for backup in db_backups[keep_count:]:
                try:
                    backup.unlink()
                    logger.debug(f"Old backup removed: {backup}")
                except IOError as e:
                    logger.warning(f"Failed to remove old backup {backup}: {e}")

            # Clean up file backups
            file_backups = sorted(self.backup_dir.glob("*_*.json"),
                                 key=lambda p: p.stat().st_mtime, reverse=True)

            for backup in file_backups[keep_count:]:
                try:
                    backup.unlink()
                    logger.debug(f"Old file backup removed: {backup}")
                except IOError as e:
                    logger.warning(f"Failed to remove old file backup {backup}: {e}")

    def _setup_backup_scheduler(self) -> None:
        """Setup automatic backup scheduler."""
        # This is a placeholder for backup scheduler implementation
        # In a real implementation, you might use APScheduler or similar
        logger.info("Backup scheduler setup (placeholder)")

    # Statistics and Monitoring

    def get_storage_stats(self) -> Dict[str, Any]:
        """Get storage statistics.

        Returns:
            Dictionary with storage statistics
        """
        with self._get_db_connection() as conn:
            cursor = conn.cursor()

            # Database stats
            cursor.execute("SELECT COUNT(*) FROM documents")
            doc_count = cursor.fetchone()[0]

            cursor.execute("SELECT COUNT(*) FROM analysis_results")
            result_count = cursor.fetchone()[0]

            cursor.execute("SELECT COUNT(*) FROM clarification_items")
            clarification_count = cursor.fetchone()[0]

            cursor.execute("SELECT COUNT(*) FROM improvement_suggestions")
            suggestion_count = cursor.fetchone()[0]

        # File system stats
        json_files = len(list(self.json_dir.glob("*.json")))
        backup_files = len(list(self.backup_dir.glob("*")))

        # Size stats
        db_size = self.db_path.stat().st_size if self.db_path.exists() else 0
        json_size = sum(f.stat().st_size for f in self.json_dir.glob("*.json"))
        backup_size = sum(f.stat().st_size for f in self.backup_dir.glob("*"))

        return {
            "database": {
                "documents": doc_count,
                "analysis_results": result_count,
                "clarification_items": clarification_count,
                "improvement_suggestions": suggestion_count,
                "size_bytes": db_size
            },
            "files": {
                "json_files": json_files,
                "backup_files": backup_files,
                "json_size_bytes": json_size,
                "backup_size_bytes": backup_size
            },
            "paths": {
                "data_directory": str(self.data_dir),
                "database_path": str(self.db_path),
                "json_directory": str(self.json_dir),
                "backup_directory": str(self.backup_dir)
            }
        }